package com.pur.zsms.check.dao.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.Query;

import net.sf.json.JSONObject;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.pur.zsms.check.dao.ICheckDao;
import com.pur.zsms.check.vo.CheckVo;
import com.pur.zsms.common.dao.GenericDao;
import com.pur.zsms.common.dao.JpaTemplate;
import com.pur.zsms.common.exception.GenericException;
import com.pur.zsms.common.support.PageController;
import com.pur.zsms.common.support.PaginationSupport;
import com.pur.zsms.common.util.CipherUtil;
import com.pur.zsms.foodmanage.food.po.FoodMaterPo;
import com.pur.zsms.foodmanage.food.po.FoodPo;
import com.pur.zsms.matermanage.mater.dao.IMaterDao;
import com.pur.zsms.matermanage.mater.po.MaterLevelPo;
import com.pur.zsms.matermanage.mater.po.MaterPo;
import com.pur.zsms.matermanage.mater.vo.MaterVo;
import com.pur.zsms.ordercustommanage.dao.ISelectFoodDao;
import com.pur.zsms.ordercustommanage.po.OrderCustFoodPo;
import com.pur.zsms.ordercustommanage.po.OrderCustPo;
import com.pur.zsms.ordercustommanage.po.OrderSupplierMaterNeedPo;
import com.pur.zsms.ordercustommanage.po.OrderSupplierMaterPo;
import com.pur.zsms.ordercustommanage.po.OrderSupplierMaterRealPo;
import com.pur.zsms.ordercustommanage.po.OrderSupplierPo;
import com.pur.zsms.ordercustommanage.vo.OrderCustVo;
import com.pur.zsms.ordercustommanage.vo.OrderStateVo;
import com.pur.zsms.pmsnmanage.menu.po.MenuPo;
import com.pur.zsms.pmsnmanage.user.po.UserPo;
import com.pur.zsms.pmsnmanage.user.po.UserRolePo;
import com.pur.zsms.pmsnmanage.user.vo.UserVo;
import com.pur.zsms.supplier.po.ShopSupplierTypePo;
import com.pur.zsms.systemsetting.datadictionary.po.CodeType;
import com.pur.zsms.unusual.dao.IUnusualDao;
import com.pur.zsms.unusual.po.OrderSupplierMaterUnusualPo;
import com.pur.zsms.unusual.vo.OrderSupplierMaterUnusualVo;

@Repository("checkDao")
public class CheckDaoImpl extends GenericDao implements ICheckDao {

	public PaginationSupport listCheck(PageController pageController, CheckVo checkVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT c.mater_id,c.mater_name,c.unit,c.specifications,c.brands,SUM(a.ord_mater_weight),SUM(a.ord_mater_value) as sumvalue,(SELECT d.describ FROM t_code d WHERE d.`code_type_id`='mater_type' AND d.code_oder = c.`mater_type`) AS materType FROM t_order_supplier_mater_reality a, t_order_custom b, t_material c WHERE return_goods = 0 AND a.ord_id = b.ord_id AND c.mater_id = a.ord_mater_id AND b.ord_date >= '"
				+ checkVo.getOrdDateStart() + "' AND b.ord_date <= '" + checkVo.getOrdDateEnd() + "'");
		if (checkVo.getMaterType() != 0) {
			ql.append(" AND c.mater_type = '" + checkVo.getMaterType() + "'");
		}
		if (checkVo.getMaterType() != 0) {
			ql.append(" AND c.mater_type =  '" + checkVo.getMaterType() + "'");
		}
		if (!"".equals(checkVo.getCustIdAll()) && !"0".equals(checkVo.getCustIdAll())) {
			ql.append(" AND b.`ord_cust_id` IN (" + checkVo.getCustIdAll() + ")");
		}
		if (StringUtils.isNotBlank(checkVo.getMaterName())) {
			ql.append(" and (c.mater_name like '%" + checkVo.getMaterName().trim() + "%' OR c.`all_spell` LIKE '%" + checkVo.getMaterName()
					+ "%' OR c.`fist_spell` LIKE '%" + checkVo.getMaterName() + "%')");
		}
		if (checkVo.getSupId() != 0) {
			ql.append(" AND a.`ord_sup_id` = '" + checkVo.getSupId() + "'");
		}
		ql.append("  GROUP BY c.mater_id ORDER BY ");
		if (checkVo.getOrderBy() == 1) {
			ql.append("  sumvalue DESC ");

		} else if (checkVo.getOrderBy() == 0) {
			ql.append("  c.mater_type,c.`all_spell` ");
		}
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport listCheckForSupplier(PageController pageController, CheckVo checkVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT c.mater_id,c.mater_name,c.unit,c.specifications,c.brands,SUM(a.ord_mater_weight),SUM(a.ord_mater_value) as sumvalue,SUM(a.ord_mater_value_cost) as sumvaluecost,(SELECT describ FROM t_code co WHERE co.code_type_id = 'mater_type' AND co.code_oder=c.mater_type) FROM t_order_supplier_mater_reality a, t_order_supplier b, t_material c WHERE return_goods = 0 AND a.ord_id = b.ord_id AND c.mater_id = a.ord_mater_id AND b.ord_date >= '"
				+ checkVo.getOrdDateStart() + "' AND b.ord_date <= '" + checkVo.getOrdDateEnd() + "'");
		if (checkVo.getMaterType() != 0) {
			ql.append(" AND c.mater_type = '" + checkVo.getMaterType() + "'");
		}
		if (checkVo.getMaterType() != 0) {
			ql.append(" AND c.mater_type =  '" + checkVo.getMaterType() + "'");
		}
		if (!"".equals(checkVo.getCustIdAll()) && !"0".equals(checkVo.getCustIdAll())) {
			ql.append(" AND b.`ord_cust_id` IN (" + checkVo.getCustIdAll() + ")");
		}

		if (checkVo.getCompanyId() != null && !"".equals(checkVo.getCompanyId()) && !"0".equals(checkVo.getCompanyId())) {
			ql.append(" and b.`ord_cust_id` IN ( SELECT d.shop_id FROM t_shop d WHERE d.company_user IN (" + checkVo.getCompanyId() + "))");
		}
		if (StringUtils.isNotBlank(checkVo.getMaterName())) {
			ql.append(" and (c.mater_name like '%" + checkVo.getMaterName().trim() + "%' OR c.`all_spell` LIKE '%" + checkVo.getMaterName()
					+ "%' OR c.`fist_spell` LIKE '%" + checkVo.getMaterName() + "%')");
		}
		if (checkVo.getSupId() != 0) {
			ql.append(" AND a.`ord_sup_id` = '" + checkVo.getSupId() + "'");
		}
		ql.append("  GROUP BY c.mater_id ORDER BY sumvalue DESC");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport findCheckDetail(PageController pageController, CheckVo checkVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("   SELECT a. ord_id , b. ord_cust_id , u.shop_name , b.ord_date , a.ord_mater_id , m.mater_name , m. specifications , m. brands , a. ord_mater_weight , m. unit , a. ord_mater_value , a. ord_sup_id , s. sup_companyname,(SELECT s2.code FROM t_supplier_shop s2 WHERE s2.sup_id = a.`ord_sup_id` AND s2.shop_id = u.`shop_id`)  FROM t_order_supplier_mater_reality a, t_order_custom b, t_shop u, t_material m, t_supplier s WHERE a. return_goods != - 1 AND a. ord_id = b. ord_id AND b. ord_cust_id = u.shop_id AND m. mater_id = a. ord_mater_id AND s. sup_id = a. ord_sup_id");
		if (checkVo.getCustIdAll() != null && !checkVo.getCustIdAll().equals("")) {
			ql.append(" AND b. ord_cust_id IN (" + checkVo.getCustIdAll() + ")");
		}
		if (checkVo.getSupId() != 0) {
			ql.append(" AND a.`ord_sup_id` = '" + checkVo.getSupId() + "'");
		}
		ql.append(" AND ord_mater_id = '" + checkVo.getMaterId() + "' AND ord_date >= '" + checkVo.getOrdDateStart() + "' AND ord_date <= '"
				+ checkVo.getOrdDateEnd() + "' ORDER BY b.ord_cust_id,b. ord_date ");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	@Override
	public List<Object> getSumValueForCheck(CheckVo checkVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT SUM(a.ord_mater_value) as sumvalue FROM t_order_supplier_mater_reality a, t_order_custom b, t_material c WHERE return_goods = 0 AND a.ord_id = b.ord_id AND c.mater_id = a.ord_mater_id AND b.ord_date >= '"
				+ checkVo.getOrdDateStart() + "' AND b.ord_date <= '" + checkVo.getOrdDateEnd() + "'");
		if (checkVo.getMaterType() != 0) {
			ql.append(" AND c.mater_type = '" + checkVo.getMaterType() + "'");
		}
		if (checkVo.getMaterType() != 0) {
			ql.append(" AND c.mater_type =  '" + checkVo.getMaterType() + "'");
		}
		if (!"".equals(checkVo.getCustIdAll()) && !"0".equals(checkVo.getCustIdAll())) {
			ql.append(" AND b.`ord_cust_id` IN (" + checkVo.getCustIdAll() + ")");
		}
		if (StringUtils.isNotBlank(checkVo.getMaterName())) {
			ql.append(" and (c.mater_name like '%" + checkVo.getMaterName().trim() + "%' OR c.`all_spell` LIKE '%" + checkVo.getMaterName()
					+ "%' OR c.`fist_spell` LIKE '%" + checkVo.getMaterName() + "%')");
		}
		if (checkVo.getSupId() != 0) {
			ql.append(" AND a.`ord_sup_id` = '" + checkVo.getSupId() + "'");
		}

		List list = this.entityManager.createNativeQuery(ql.toString()).getResultList();

		return list;
	}

	public List<Object[]> getListCheckForExcel(CheckVo checkVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT c.mater_id,c.mater_name,c.unit,c.specifications,c.brands,  REPLACE( FORMAT (SUM(a.ord_mater_weight),2),',',''),REPLACE( FORMAT (SUM(a.ord_mater_value),2),',','')  AS sumvalue,REPLACE( FORMAT (SUM(a.ord_mater_value_cost),2),',','')AS sumvaluecost,(SELECT d.describ FROM t_code d WHERE d.`code_type_id`='mater_type' AND d.code_oder = c.`mater_type`) AS materType,(SELECT describ FROM t_code co WHERE co.code_type_id = 'mater_type' AND co.code_oder=c.mater_type)  FROM t_order_supplier_mater_reality a, t_order_supplier b, t_material c WHERE return_goods = 0 AND a.ord_id = b.ord_id AND c.mater_id = a.ord_mater_id AND b.ord_date >= '"
				+ checkVo.getOrdDateStart() + "' AND b.ord_date <= '" + checkVo.getOrdDateEnd() + "'");
		if (checkVo.getMaterType() != 0) {
			ql.append(" AND c.mater_type = '" + checkVo.getMaterType() + "'");
		}
		if (checkVo.getMaterType() != 0) {
			ql.append(" AND c.mater_type =  '" + checkVo.getMaterType() + "'");
		}
		if (!"".equals(checkVo.getCustIdAll()) && !"0".equals(checkVo.getCustIdAll())) {
			ql.append(" AND b.`ord_cust_id` IN (" + checkVo.getCustIdAll() + ")");
		}

		if (checkVo.getCompanyId() != null && !"".equals(checkVo.getCompanyId()) && !"0".equals(checkVo.getCompanyId())) {
			ql.append(" and b.`ord_cust_id` IN ( SELECT d.shop_id FROM t_shop d WHERE d.company_user IN (" + checkVo.getCompanyId() + "))");
		}

		if (StringUtils.isNotBlank(checkVo.getMaterName())) {
			ql.append(" and (c.mater_name like '%" + checkVo.getMaterName().trim() + "%' OR c.`all_spell` LIKE '%" + checkVo.getMaterName()
					+ "%' OR c.`fist_spell` LIKE '%" + checkVo.getMaterName() + "%')");
		}
		if (checkVo.getSupId() != 0) {
			ql.append(" AND a.`ord_sup_id` = '" + checkVo.getSupId() + "'");
		}
		ql.append("  GROUP BY c.mater_id ORDER BY ");
		if (checkVo.getOrderBy() == 1) {
			ql.append("  sumvalue DESC ");

		} else if (checkVo.getOrderBy() == 0) {
			ql.append("  c.mater_type,c.`all_spell` ");
		}

		List list = this.entityManager.createNativeQuery(ql.toString()).getResultList();

		return list;
	}

	public List<Object[]> getListCheckForExcelDetail(CheckVo checkVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("   SELECT a. ord_id , b. ord_cust_id , u.shop_name , b. ord_date , a. ord_mater_id , m. mater_name , m. specifications , m. brands , a. ord_mater_weight , m. unit , a. ord_mater_value , a. ord_sup_id , s. sup_companyname FROM t_order_supplier_mater_reality a, t_order_custom b, t_shop u, t_material m, t_supplier s WHERE a. return_goods != - 1 AND a. ord_id = b. ord_id AND b. ord_cust_id = u.shop_id AND m. mater_id = a. ord_mater_id AND s. sup_id = a. ord_sup_id");
		if (checkVo.getCustIdAll() != null && !checkVo.getCustIdAll().equals("")) {
			ql.append(" AND b. ord_cust_id IN (" + checkVo.getCustIdAll() + ")");
		}
		ql.append(" AND ord_mater_id = '" + checkVo.getMaterId() + "' AND ord_date >= '" + checkVo.getOrdDateStart() + "' AND ord_date <= '"
				+ checkVo.getOrdDateEnd() + "' ORDER BY b.ord_cust_id,b. ord_date ");

		List list = this.entityManager.createNativeQuery(ql.toString()).getResultList();

		return list;
	}

	// 供货商销售报表
	public List<Object[]> getSalesVolume(CheckVo checkVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT SUM(a.ord_mater_weight), a.ord_mater_id, b.ord_date, ");
		// ql.append("DATE_FORMAT( b.ord_date, '%x年-%m月-第%v周' ) AS timestring ");
		if (checkVo.getTimeType() == 1) {
			// 年
			ql.append("DATE_FORMAT( b.ord_date, '%Y' ) AS timestring ");
		} else if (checkVo.getTimeType() == 2) {
			// 月
			ql.append("DATE_FORMAT( b.ord_date, '%m/01/%Y' ) AS timestring ");
		} else if (checkVo.getTimeType() == 3) {
			// 周
			ql.append("DATE_FORMAT( b.ord_date, '%x.%v' ) AS timestring ");
		} else if (checkVo.getTimeType() == 4) {
			// 日
			ql.append("DATE_FORMAT( b.ord_date, '%m/%d/%Y' ) AS timestring ");
		}

		ql.append(" FROM t_order_supplier_mater_reality a, t_order_supplier b WHERE a.ord_id = b.ord_id AND a.ord_sup_id = b.ord_sup_id AND a.return_goods != 1 ");
		if (checkVo.getSupId() != 0) {
			ql.append(" AND b.`ord_sup_id` = '" + checkVo.getSupId() + "'");
		}
		ql.append(" AND a.ord_mater_id = '" + checkVo.getMaterId() + "' AND b.ord_date >= '" + checkVo.getOrdDateStart() + "' AND b.ord_date <= '"
				+ checkVo.getOrdDateEnd() + "' GROUP BY timestring  ");

		List<Object[]> list = this.entityManager.createNativeQuery(ql.toString()).getResultList();
		return list;
	}

	public List<Object[]> getSalesVolumeForExcel(CheckVo checkVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT SUM(a.ord_mater_weight), a.ord_mater_id, b.ord_date, ");
		// ql.append("DATE_FORMAT( b.ord_date, '%x年-%m月-第%v周' ) AS timestring ");
		if (checkVo.getTimeType() == 1) {
			// 年
			ql.append("DATE_FORMAT( b.ord_date, '%Y年' ) AS timestring ");
		} else if (checkVo.getTimeType() == 2) {
			// 月
			ql.append("DATE_FORMAT( b.ord_date, '%Y年%m月' ) AS timestring ");
		} else if (checkVo.getTimeType() == 3) {
			// 周
			ql.append("DATE_FORMAT( b.ord_date, '%x年 第%v周' ) AS timestring ");
		} else if (checkVo.getTimeType() == 4) {
			// 日
			ql.append("DATE_FORMAT( b.ord_date, '%Y年%m月%d日' ) AS timestring ");
		}

		ql.append(" FROM t_order_supplier_mater_reality a, t_order_supplier b WHERE a.ord_id = b.ord_id AND a.ord_sup_id = b.ord_sup_id AND a.return_goods != 1 ");
		if (checkVo.getSupId() != 0) {
			ql.append(" AND b.`ord_sup_id` = '" + checkVo.getSupId() + "'");
		}
		ql.append(" AND a.ord_mater_id = '" + checkVo.getMaterId() + "' AND b.ord_date >= '" + checkVo.getOrdDateStart() + "' AND b.ord_date <= '"
				+ checkVo.getOrdDateEnd() + "' GROUP BY timestring  ");

		List<Object[]> list = this.entityManager.createNativeQuery(ql.toString()).getResultList();
		return list;
	}

}
